<?php
class m_reportchecklist extends CI_Model {
	
	function data(){
		$sql = "SELECT project_name, project_code,
        tbl1.t_project_id , concat(tbl1.document_noflow, tbl1.document_noproyek) doc_no,
tbl1.document_no no1, tbl1.div_name div1,
tbl2.document_no no2, tbl2.div_name div2,
tbl3.document_no no3, tbl3.div_name div3,
tbl4.document_no no4, tbl4.div_name div4,
tbl5.document_no no5, tbl5.div_name div5,
tbl6.document_no no6, tbl6.div_name div6,
tbl7.document_no no7, tbl7.div_name div7 FROM
    (select document_noflow,
    
    if ((posisi - 1) >= maxpos , 'Selesai' , div_name) as div_name,
    document_noproyek, 
    t_document.t_project_id, t_document.document_no, t_document.document_id 
	from t_document
	left join v_posdoc on t_document.document_id = v_posdoc.document_id
	left join m_flowdet on flowdet_seq = posisi and m_flowdet.m_flow_id = t_document.m_flow_id
	left join m_div on m_div_id = div_id
 where t_document.m_flow_id = 1 group by document_noproyek ) tbl1 ";
        for($i=2;$i<=7;$i++){
$sql .= "LEFT JOIN (select document_noflow,
group_concat(if ((posisi - 1) >= maxpos , 'Selesai' , div_name) )as div_name, document_noproyek, 
    t_document.t_project_id, group_concat(t_document.document_no)as document_no, t_document.document_id 
	from t_document
	left join v_posdoc on t_document.document_id = v_posdoc.document_id
	left join m_flowdet on flowdet_seq = posisi and m_flowdet.m_flow_id = t_document.m_flow_id
	left join m_div on m_div_id = div_id
 where t_document.m_flow_id = ".$i."  group by document_noproyek  ) tbl".$i."
 ON tbl1.t_project_id = tbl".$i.".t_project_id ";
        }  
$sql .= " RIGHT JOIN t_project 
ON tbl1.t_project_id = project_id
LEFT JOIN t_userproject
ON t_userproject.t_project_id = t_project.project_id 
WHERE 1=1 ";
        
            if (strtolower($_SESSION[SESSION_GROUPNAMA]) == "cabang" )
                 $sql .=    "  AND t_project.m_cabang_id = ".$_SESSION[SESSION_CABANGID]." ";
             else if (strtolower($_SESSION[SESSION_GROUPNAMA]) == "proyek" )
                 $sql .=    "  AND t_userproject.s_users_id = '".$_SESSION[SESSION_USERID]."' ";
    
$sql .= "order by 1";
     $this->firephp->log($sql);
		$query = $this->db->query($sql);
		$result = $query->result();
		return $result;
	}
	
}